package org.zty.db;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


public class JDBC {
	public JDBC() {
	}

	/** 
     * 获取某种数据库中中用户的所有表 
     * @param type 
     * @param database 
     * @return 
     */  
    public static List<String[]> getTableDesc(String curTable,String driver,String url,String username,String password) throws SQLException{
    	if(!loadDriver(driver)){
			return new ArrayList<>();
		}
		Connection cnn = getConnection(url, username, password);
		List<String[]> tablenameList = new ArrayList<String[]>();
    	if(driver.toLowerCase().contains("oracle")){
			tablenameList = getTableDesc(cnn, curTable);
		}else if(driver.toLowerCase().contains("jtds")){
			//TODO
			tablenameList = getTableDesc(cnn, curTable);;
		}else{
			//TODO
			tablenameList = getTableDesc(cnn, curTable);
		}
    	return tablenameList;
    }  
    
    /**
     * Mysql的获取主键的值
     * @param curTable
     * @param driver
     * @param url
     * @param username
     * @param password
     * @return
     */
    public static String getPrimaryKey(String curTable,String driver,String url,String username,String password){
    	
    	if(!loadDriver(driver)){
			return null;
		}
		Connection cnn = getConnection(url, username, password);
		DatabaseMetaData dbMeta;
		try {
			dbMeta = cnn.getMetaData();
			ResultSet pkRSet = dbMeta.getPrimaryKeys(null, null,curTable);
			String primaryKeyColumnName = null;
			int i = 0;
			while(pkRSet.next()){
				i++;
				primaryKeyColumnName = pkRSet.getString("COLUMN_NAME");  
				if(i>1){
					System.out.println("警告:表"+curTable+"有联合组件!!!! 请手动处理");
				}
			}
			return primaryKeyColumnName;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return null;
    }
    public static List<String[]> getTableDesc(Connection cnn,String curTable) throws SQLException{
    	DatabaseMetaData dbmd = cnn.getMetaData(); 
    	String columnName; 
    	String columnType; 
    	ResultSet colRet = dbmd.getColumns(null,"%", curTable,"%"); 
    	List<String[]> tableDesc = new ArrayList<String[]>();
    	while(colRet.next()) {
    		
	    	columnName = colRet.getString("COLUMN_NAME"); 
	    	columnType = colRet.getString("TYPE_NAME"); 
	    	int datasize = colRet.getInt("COLUMN_SIZE"); 
	    	int digits = colRet.getInt("DECIMAL_DIGITS"); 
	    	int nullable = colRet.getInt("NULLABLE");
	    	//System.out.println(columnName+" "+columnType+" "+datasize+" "+digits+" "+  nullable); 
	    	String[] strArray = new String[]{columnName,columnType,datasize+"",digits+"",nullable+""};
	    	tableDesc.add(strArray);
    	}
    	return tableDesc;
    }
    
    
	public static List<String> getAllTableName(String driver,String url,String username,String password) throws SQLException{
		if(!loadDriver(driver)){
			return new ArrayList<>();
		}
		Connection cnn = getConnection(url, username, password);
		List<String> tablenameList = new ArrayList<String>();
		
		if(driver.toLowerCase().contains("oracle")){
			tablenameList = getAllTableNameOracle(cnn);
		}else if(driver.toLowerCase().contains("jtds")){
			tablenameList = getAllTableNameSqlserver(cnn);
		}else if(driver.toLowerCase().contains("mysql")){
			tablenameList = getAllTableNameMysql(cnn);
		}
		
		
		return tablenameList;
	}
	
	/** 
     * 获取某种数据库中中用户的所有表 
     * @param type 
     * @param database 
     * @return 
     */  
    public static List<String> getAllTableNameSqlserver(Connection cnn){  
          
        List<String> list = null;  
            //查询所有用户表  
            StringBuffer sql = new StringBuffer("Select NAME FROM ")  
                               .append("..SysObjects Where XType='U' orDER BY Name");  
            Statement sta = null;  
            ResultSet rs = null;  
            try {  
                sta = cnn.createStatement();  
                rs = sta.executeQuery(sql.toString());  
                while(rs.next()){  
                    if(list == null){  
                        list = new ArrayList<String>();  
                    }  
                    list.add(rs.getString("NAME"));  
                }  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }finally{  
                try {  
                    if(rs != null)  
                        rs.close();  
                    if(sta != null)  
                        sta.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
        return list;  
    }  
	
	public static List getAllTableNameOracle(Connection cnn) throws SQLException {
		List tables = new ArrayList();

		DatabaseMetaData dbMetaData = cnn.getMetaData();

		// 可为:"TABLE", "VIEW", "SYSTEM TABLE",
		// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
		String[] types = { "TABLE" };

		ResultSet tabs = dbMetaData.getTables(null, dbMetaData.getUserName(),"%", types/* 只要表就好了 */);
		/*
		 * 记录集的结构如下: TABLE_CAT String => table catalog (may be null) TABLE_SCHEM
		 * String => table schema (may be null) TABLE_NAME String => table name
		 * TABLE_TYPE String => table type. REMARKS String => explanatory
		 * comment on the table TYPE_CAT String => the types catalog (may be
		 * null) TYPE_SCHEM String => the types schema (may be null) TYPE_NAME
		 * String => type name (may be null) SELF_REFERENCING_COL_NAME String =>
		 * name of the designated "identifier" column of a typed table (may be
		 * null) REF_GENERATION String => specifies how values in
		 * SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER",
		 * "DERIVED". (may be null)
		 */
		while (tabs.next()) {
			// 只要表名这一列
			tables.add(tabs.getObject("TABLE_NAME"));

		}
		System.out.println(tables);
		return tables;

	}
	
	public static List getAllTableNameMysql(Connection cnn) throws SQLException {
		List tables = new ArrayList();

		DatabaseMetaData dbMetaData = cnn.getMetaData();

		// 可为:"TABLE", "VIEW", "SYSTEM TABLE",
		// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
		String[] types = { "TABLE" };

		ResultSet tabs = dbMetaData.getTables(null, dbMetaData.getUserName(),"%", types/* 只要表就好了 */);
		/*
		 * 记录集的结构如下: TABLE_CAT String => table catalog (may be null) TABLE_SCHEM
		 * String => table schema (may be null) TABLE_NAME String => table name
		 * TABLE_TYPE String => table type. REMARKS String => explanatory
		 * comment on the table TYPE_CAT String => the types catalog (may be
		 * null) TYPE_SCHEM String => the types schema (may be null) TYPE_NAME
		 * String => type name (may be null) SELF_REFERENCING_COL_NAME String =>
		 * name of the designated "identifier" column of a typed table (may be
		 * null) REF_GENERATION String => specifies how values in
		 * SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER",
		 * "DERIVED". (may be null)
		 */
		while (tabs.next()) {
			// 只要表名这一列
			tables.add(tabs.getObject("TABLE_NAME"));

		}
		System.out.println(tables);
		return tables;

	}

	public static boolean loadDriver(String driver){
		try {
			Class.forName(driver);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	/**
	 * 获取数据库连接
	 */
	public static Connection getConnection(String url,String username,String password) {
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	/**
	 * 获取statement对象，操作数据库，处理返回结果
	 *//*
	public static void process(String sql) {
		Connection con = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			if (ps.execute()) {
				rs = ps.getResultSet();
				// printResultSet(rs);
			} else {
				int i = ps.getUpdateCount();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
	}

	*//**
	 * 获取statement对象，操作数据库，处理返回结果
	 *//*
	public static List<?> process(String sql, Class clazz) {
		Connection con = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<?> list = null;
		try {
			ps = con.prepareStatement(sql);
			if (ps.execute()) {
				rs = ps.getResultSet();
				// printResultSet(rs);
				list = populate(rs, clazz);
			} else {
				int i = ps.getUpdateCount();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, con);
		}
		return list;
	}*/

	/**
	 * 处理返回结果集
	 */
	public static void printResultSet(ResultSet rs) {
		if (rs == null) {
			return;
		}
		try {
			ResultSetMetaData meta = rs.getMetaData();
			int cols = meta.getColumnCount();
			StringBuffer b = new StringBuffer();
			while (rs.next()) {
				for (int i = 1; i <= cols; i++) {
					b.append(meta.getColumnName(i) + "=");
					b.append(rs.getString(i) + "\t");
				}
				b.append("\\r\\n");
			}
			System.out.print(b.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			rs = null;
		} catch (Exception e) {
		}
		try {
			if (stmt != null) {
				stmt.close();
			}
			stmt = null;
		} catch (Exception e) {
		}
		try {
			if (conn != null) {
				conn.close();
			}
			conn = null;
		} catch (Exception e) {
		}
	}

	public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			rs = null;
		} catch (Exception e) {
		}
		try {
			if (pstmt != null) {
				pstmt.close();
			}
			pstmt = null;
		} catch (Exception e) {
		}
		try {
			if (conn != null) {
				conn.close();
			}
			conn = null;
		} catch (Exception e) {
		}
	}

	public static List populate(ResultSet rs, Class clazz)
			throws SQLException, InstantiationException, IllegalAccessException {
		// 结果集的元素对象
		ResultSetMetaData rsmd = rs.getMetaData();
		// 获取结果集的元素个数
		int colCount = rsmd.getColumnCount();
		// System.out.println("#");
		// for(int i = 1;i<=colCount;i++){
		// System.out.println(rsmd.getColumnName(i));
		// System.out.println(rsmd.getColumnClassName(i));
		// System.out.println("#");
		// }
		// 返回结果的列表集合
		List list = new ArrayList();
		// 业务对象的属性数组
		Field[] fields = clazz.getDeclaredFields();
		while (rs.next()) {// 对每一条记录进行操作
			Object obj = clazz.newInstance();// 构造业务对象实体
			// 将每一个字段取出进行赋值
			for (int i = 1; i <= colCount; i++) {
				Object value = rs.getObject(i);
				// 寻找该列对应的对象属性
				for (int j = 0; j < fields.length; j++) {
					Field f = fields[j];
					// 如果匹配进行赋值
					if (f.getName().equalsIgnoreCase(rsmd.getColumnName(i))) {
						boolean flag = f.isAccessible();
						f.setAccessible(true);
						f.set(obj, value);
						f.setAccessible(flag);
					}
				}
			}
			list.add(obj);
		}
		return list;
	}

	
	public static void main(String[] args) throws SQLException {
		//List<String> l = getAllTableName("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:127.0.0.1:1521:ORCL", "card_practise", "111111");
//		List<String> l = getAllTableName("net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://localhost:1433/ceshigo", "sa", "12qwaszxQq");
		
		List<String[]> l = getTableDesc("TBL_ORGANIZATION","oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:127.0.0.1:1521:ORCL", "card_practise", "111111");
		/*for(String str : l ){
			System.out.println(str);
		}*/
	}
}